package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.Major;
import cn.edu.sdjzu.xg.bysj.service.DepartmentService;
import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.TreeSet;

public final class MajorDao {
    //MajorDoa实例对象
    private static MajorDao majorDao = new MajorDao();
    //数据库连接
    private Connection connection;
    //预编译语句盒子
    private PreparedStatement preparedStatement;
    //结果集
    private ResultSet resultSet;
    //唯一私有构造器，避免外界新建对象
    private MajorDao() {
    }

    //获得实例
    public static MajorDao getInstance() {
        return majorDao;
    }

    //根据id查找
    public Major find(int id) throws SQLException {
        //获得数据库链接
        connection = JdbcHelper.getConn();
        Major foundMajor = null;
        //预编译sql语句
        preparedStatement = connection.prepareStatement("SELECT * FROM major WHERE id=?");
        //为sql语句参数赋值
        preparedStatement.setInt(1,id);
        //执行sql语句并获得结果集
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            //获得查找到得数据
            int major_id = resultSet.getInt("id");
            int department_id = resultSet.getInt("department_id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Department department = DepartmentService.getInstance().find(department_id);
            //新建对象，将查找到的数据用于构建对象
            foundMajor = new Major(major_id,description,no,remarks,department);
        }
        //关闭链接
        JdbcHelper.close(resultSet,preparedStatement,connection);
        //返回查找到的对象
        return foundMajor;
    }

    //查找全部
    public Collection<Major> findAll() throws SQLException {
        //新建集合用于储存查到的数据集合
        Collection<Major> majors = new TreeSet<Major>();
        //获得数据库链接
        connection = JdbcHelper.getConn();
        //预编译sql语句
        preparedStatement = connection.prepareStatement("SELECT * FROM major");
        //执行sql语句并获得结果集
        resultSet = preparedStatement.executeQuery();
        //遍历结果集
        while (resultSet.next()){
            Major foundMajor = null;
            //获得数据
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            Department department = DepartmentService.getInstance().find(resultSet.getInt("department_id"));
            //新建foundMajor对象，将查找到的数据用于构建对象
            foundMajor = new Major(id,description,no,remarks,department);
            //将foundMajor添加进结果集
            majors.add(foundMajor);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement,connection);
        //返回查找到的结果集
        return majors;
    }

    //更新
    public boolean update(Major major) throws SQLException {
        //获得数据库链接
        connection = JdbcHelper.getConn();
        //预编译sql语句
        preparedStatement = connection.prepareStatement("UPDATE major SET description=?,no=?,remarks=?,department_id=? where id=?");
        //为sql语句参数赋值
        preparedStatement.setString(1,major.getDescription());
        preparedStatement.setString(2,major.getNo());
        preparedStatement.setString(3,major.getRemarks());
        preparedStatement.setInt(4,major.getDepartment().getId());
        preparedStatement.setInt(5,major.getId());
        //执行sql语句并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement,connection);
        //返回修改判定
        return rowAffected >0;
    }

    //添加
    public boolean add(Major major) throws SQLException {
        //获得数据库链接
        connection = JdbcHelper.getConn();
        //预编译sql语句
        preparedStatement = connection.prepareStatement("INSERT INTO major (description,no,remarks,department_id)VALUES (?,?,?,?)");
        //为sql语句参数赋值
        preparedStatement.setInt(4,major.getDepartment().getId());
        preparedStatement.setString(1,major.getDescription());
        preparedStatement.setString(2,major.getNo());
        preparedStatement.setString(3,major.getRemarks());
        //执行sql语句并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement,connection);
        //返回更新判定
        return rowAffected >0;
    }

    //删除
    public boolean delete(Integer id) throws SQLException {
        //获得数据库链接
        connection = JdbcHelper.getConn();
        //预编译sql语句
        preparedStatement = connection.prepareStatement("DELETE FROM major WHERE id=?");
        //为sql语句参数赋值
        preparedStatement.setInt(1,id);
        //执行sql语句并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement,connection);
        //返回删除判定
        return rowAffected >0;
    }

    public boolean delete(Major major) throws SQLException {
        //获得major的id，根据id删除
        return this.delete(major.getId());
    }
}
